USE [KCL]
GO

/****** Object:  UserDefinedFunction [dbo].[GetNextProductCode]    Script Date: 11/27/2011 18:31:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Chau Tieu
-- =============================================
CREATE FUNCTION [dbo].[GetNextProductCode] ()
RETURNS VARCHAR(6)
AS
BEGIN
	DECLARE @NextID INT;
	DECLARE @NextProductCode VARCHAR(6);
	
	SELECT @NextID = (ISNULL(MAX(CONVERT(INT, RIGHT(code, 4))), -1) + 1)
	FROM Product
	WHERE code LIKE 'SP%' 
		AND LEN(code) = 6
		AND ISNUMERIC(RIGHT(code, 4)) = 1;
		
	SET @NextProductCode = 'SP' + REPLICATE('0', 4 - LEN(@NextID)) + CONVERT(VARCHAR, @NextID);
	RETURN @NextProductCode;
END

GO

